﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;

namespace Bitirme.DAL.Data_Access
{
    public class PackageRequirementCollection
    {
        public static int InsertPackageRequirement(WorkPackage workpackage)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parWorkPackageID = null;
            SqlParameter parCapabilityID = null;
            SqlParameter parCost = null;
            SqlParameter parManHour = null;
            SqlParameter parReturnValue = null;

            int insertedPackageReqID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertPackageRequirement", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@workPackageID";
                parWorkPackageID.Value = workpackage.ID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;

                parCapabilityID = new SqlParameter();
                parCapabilityID.ParameterName = "@capabilityID";
                parCapabilityID.Value = workpackage.Requirement.RequirementCapability.ID;
                parCapabilityID.SqlDbType = SqlDbType.Int;

                parCost = new SqlParameter();
                parCost.ParameterName = "@cost";
                parCost.Value = workpackage.Requirement.Cost;
                parCost.SqlDbType = SqlDbType.Int;

                parManHour = new SqlParameter();
                parManHour.ParameterName = "@manhour";
                parManHour.Value = workpackage.Requirement.ManHour;
                parManHour.SqlDbType = SqlDbType.Int;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";

                cmd.Parameters.Add(parWorkPackageID);
                cmd.Parameters.Add(parCapabilityID);
                cmd.Parameters.Add(parCost);
                cmd.Parameters.Add(parManHour);
                cmd.Parameters.Add(parReturnValue);
                
                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedPackageReqID);
            }
            catch (SqlException e)
            {
                insertedPackageReqID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return insertedPackageReqID;
        }
        public static Boolean UpdatePackageRequirementByID(PackageRequirement packagereq, WorkPackage workpackage)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            SqlParameter parWorkPackageID = null;
            SqlParameter parCapabilityID = null;
            SqlParameter parCost = null;
            SqlParameter parManHour = null;

            bool updatedPackageReqID = false;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("UpdatePackageRequirementByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = packagereq.ID;
                parID.SqlDbType = SqlDbType.Int;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@workPackageID";
                parWorkPackageID.Value = workpackage.ID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;

                parCapabilityID = new SqlParameter();
                parCapabilityID.ParameterName = "@capabilityID";
                parCapabilityID.Value = packagereq.RequirementCapability.ID;
                parCapabilityID.SqlDbType = SqlDbType.Int;

                parCost = new SqlParameter();
                parCost.ParameterName = "@cost";
                parCost.Value = packagereq.Cost;
                parCost.SqlDbType = SqlDbType.Int;

                parManHour = new SqlParameter();
                parManHour.ParameterName = "@manhour";
                parManHour.Value = workpackage.Requirement.ManHour;
                parManHour.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parWorkPackageID);
                cmd.Parameters.Add(parCapabilityID);
                cmd.Parameters.Add(parCost);
                cmd.Parameters.Add(parManHour);

                myConnection.Open();
                cmd.ExecuteScalar();
                updatedPackageReqID = true;
            }
            catch (SqlException e)
            {
                updatedPackageReqID = false;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return updatedPackageReqID;
        }
        public static Boolean DeletePackageRequirementByID(int packagerequirementID)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parPackagereqID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("PackageRequirementByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parPackagereqID = new SqlParameter();
                parPackagereqID.ParameterName = "@ID";
                parPackagereqID.Value = packagerequirementID;
                parPackagereqID.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parPackagereqID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }
        public static Boolean DeletePackageRequirementByWorkPackageID(int workPackageID) {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parWorkPackageID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeletePackageRequirementByWorkPackageID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@ID";
                parWorkPackageID.Value = workPackageID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parWorkPackageID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }
    }
}